import openpyxl
import datetime
import operator
import os
import sys

dirPath = os.path.dirname(os.path.realpath(sys.executable))
# dirPath = 'E:\\日常工作\\10月\\12\\每日必办'
print(dirPath)

fileWeiJieJue = dirPath + '\\全部工单.xlsx'
fileResultSave = dirPath + '\\每日必办文案-今日工单情况.txt'
fileResultContent = "今日工单情况\r\n"

bookTodayMustDo = openpyxl.load_workbook(fileWeiJieJue)
sheetNamesTodayMustDo = bookTodayMustDo.sheetnames
# bookWorkOrderDispatch.active
print('Sheet名称：{}'.format(sheetNamesTodayMustDo[1]))  # 打印 第二个Sheet页名称
sheetTodayMustDo = bookTodayMustDo[sheetNamesTodayMustDo[1]]
maxRowTodayMustDo = sheetTodayMustDo.max_row
maxColTodayMustDo = sheetTodayMustDo.max_column
print('maxRowWeiJieJue:{}'.format(maxRowTodayMustDo))   # 打印表的行数
print('maxColWeiJieJue:{}'.format(maxColTodayMustDo))   # 打印表的列数
print(fileResultContent)


# 获取某一列的索引号 col_name 列名
def get_col_index(sheet_source, col_name):
    maxColSheetSource = sheet_source.max_column
    for colSource in range(1, maxColSheetSource+1):
        colCellValue = sheet_source.cell(1, colSource).value
        if colCellValue is not None:
            colCellValue = colCellValue.strip()
            if colCellValue == col_name:
                return colSource


# 修改特定的承办部门
def xiu_gai_cheng_ban_bu_men(sheet_source, col_title, col_source, col_dest):
    colTitleIndex = get_col_index(sheet_source, col_title)
    maxRowCur = sheet_source.max_row
    for rowCur in range(2, maxRowCur+1):
        cellValueSearch = sheet_source.cell(rowCur, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            if cellValueSearch == col_source:
                sheet_source.cell(rowCur, colTitleIndex).value = col_dest


# 修改承办部门
xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '应急小分队', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '流动人口管理组', '社会治理组')
xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
# xiu_gai_cheng_ban_bu_men(sheetWeiJieJue, '承办部门', '送变电公司社区', '送变电社区')

# 计算 双满工单数量 解决基本满意数量 解决不满意数量
colIndexChengBan = get_col_index(sheetTodayMustDo, '承办部门')
colIndexJieDan = get_col_index(sheetTodayMustDo, '接单部门')

# 社区（村）
numSheQuCun = 0
keyListSheQuCun = []
tinyDictSheQuCun = {}
# 拱辰街道机关科室
numJiGuanKeShi = 0
keyListJiGuanKeShi = []
tinyDictJiGuanKeShi = {}

for rowSource in range(2, maxRowTodayMustDo + 1):
    cellJieDan = sheetTodayMustDo.cell(rowSource, colIndexJieDan).value
    cellChengBan = sheetTodayMustDo.cell(rowSource, colIndexChengBan).value
    isFindChengGuan = cellChengBan.find('综合城管')
    if isFindChengGuan == 0:
        cellChengBan = '综合行政执法队'
    if cellJieDan is not None:
        cellJieDan = cellJieDan.strip()
        if cellChengBan is not None:
            cellChengBan = cellChengBan.strip()
            if cellJieDan == '拱辰街道机关科室':
                numJiGuanKeShi = numJiGuanKeShi + 1
                if cellChengBan in keyListJiGuanKeShi:
                    tinyDictJiGuanKeShi[cellChengBan] = tinyDictJiGuanKeShi[cellChengBan] +1
                else:
                    keyListJiGuanKeShi.append(cellChengBan)
                    tinyDictJiGuanKeShi[cellChengBan] = 1
            else:
                if cellJieDan == '社区（村）':
                    numSheQuCun = numSheQuCun + 1
                    if cellChengBan in keyListSheQuCun:
                        tinyDictSheQuCun[cellChengBan] = tinyDictSheQuCun[cellChengBan] +1
                    else:
                        keyListSheQuCun.append(cellChengBan)
                        tinyDictSheQuCun[cellChengBan] = 1


print('涉及机关科室{}件'.format(numJiGuanKeShi))
print('涉及社区（村）{}件'.format(numSheQuCun))

if numJiGuanKeShi + numSheQuCun > 0:
    fileResultContent = fileResultContent + '\n应解决工单{}件。'.format(numJiGuanKeShi + numSheQuCun)
    if numJiGuanKeShi > 0:
        fileResultContent = fileResultContent + '涉及机关科室{}件（'.format(numJiGuanKeShi)
        tinyDictJiGuanKeShi = sorted(tinyDictJiGuanKeShi.items(), key=lambda kv: kv[1], reverse=True)
        print('{}'.format(tinyDictJiGuanKeShi))
        numLast = 0
        numCount = 0
        numIndex = 0
        for keyNoKeShi in tinyDictJiGuanKeShi:
            numIndex = numIndex+1
            if numLast == 0:
                numLast = keyNoKeShi[1]
            else:
                if numLast != keyNoKeShi[1]:
                    if numCount > 1:
                        fileResultContent = fileResultContent + '各{}件，'.format(numLast)
                    else:
                        fileResultContent = fileResultContent + '{}件，'.format(numLast)
                    numCount = 0
            if numLast == keyNoKeShi[1]:
                if numCount == 0:
                    fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
                else:
                    fileResultContent = fileResultContent + '、{}'.format(keyNoKeShi[0])
            else:
                fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
            numCount = numCount+1
            numLast = keyNoKeShi[1]
        if numCount > 1:
            fileResultContent = fileResultContent + '各{}件'.format(numLast)
        else:
            fileResultContent = fileResultContent + '{}件'.format(numLast)
        fileResultContent = fileResultContent + '）。'

    if numSheQuCun > 0:
        fileResultContent = fileResultContent + '\n涉及社区（村）{}件（'.format(numSheQuCun)
        tinyDictSheQuCun = sorted(tinyDictSheQuCun.items(), key=lambda kv: kv[1], reverse=True)
        print('{}'.format(tinyDictSheQuCun))
        numLastSheQu = 0
        numIndexSheQu = 0
        numCountSheQu = 0
        for keyNoSheQu in tinyDictSheQuCun:
            numIndexSheQu = numIndexSheQu + 1
            if numLastSheQu == 0:
                numLastSheQu = keyNoSheQu[1]
            else:
                if numLastSheQu != keyNoSheQu[1]:
                    if numCountSheQu > 1:
                        fileResultContent = fileResultContent + '各{}件，'.format(numLastSheQu)
                    else:
                        fileResultContent = fileResultContent + '{}件，'.format(numLastSheQu)
                    numCountSheQu = 0
            if numLastSheQu == keyNoSheQu[1]:
                if numCountSheQu == 0:
                    fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
                else:
                    fileResultContent = fileResultContent + '、{}'.format(keyNoSheQu[0])
            else:
                fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
            numCountSheQu = numCountSheQu+1
            numLastSheQu = keyNoSheQu[1]
        if numCountSheQu > 1:
            fileResultContent = fileResultContent + '各{}件'.format(numLastSheQu)
        else:
            fileResultContent = fileResultContent + '{}件'.format(numLastSheQu)
        fileResultContent = fileResultContent + '）。'

print('{}'.format(fileResultContent))

with open(fileResultSave, "w") as f:
    f.write(fileResultContent)
